package org.xiyinjihe.platform.util;

import com.alibaba.fastjson.JSON;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.StringUtils;

import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;


public class ExcelUtils {

    private static final Logger logger  = Logger.getLogger(ExcelUtils.class);

    /**
     * 读取EXCEL 数据
     *
     * @param inputStream 输入流
     * @param dataKeys    表格列名对应的字段名
     * @param clazz       要解析到对应JAVA对象的类
     * @param <T>
     * @param numOfSheet 表格序号
     * @return
     * @throws Exception
     */
    public static <T> List<T> readExcel(InputStream inputStream, List<String> dataKeys, Class<T> clazz,int numOfSheet) throws Exception {
        if (inputStream == null || dataKeys == null || dataKeys.size() == 0) {
            throw new Exception("读取数据异常！");
        }
        List<LinkedHashMap<String, Object>> dataMapList = new ArrayList<>();
        LinkedHashMap<String, Object> dataMap = null;

        Workbook hssfWorkbook = WorkbookFactory.create(inputStream);
        //Sheet hssfSheet = workbook.getSheetAt(0);  //示意访问sheet
        // XSSFWorkbook hssfWorkbook = new XSSFWorkbook(inputStream);
        // 循环工作表Sheet
        //int numberOfSheets = hssfWorkbook.getNumberOfSheets();
        //for (int numSheet = 0; numSheet < numberOfSheets; numSheet++) {
        Sheet hssfSheet = hssfWorkbook.getSheetAt(numOfSheet);
        if (hssfSheet == null) {
            throw new Exception("读取数据异常！");
        }
        // 循环行Row
        int numberOfRows = hssfSheet.getLastRowNum();
        for (int rowNum = 1; rowNum <= numberOfRows; rowNum++) {
            Row hssfRow = hssfSheet.getRow(rowNum);
            if (hssfRow != null && StringUtils.hasText(getValue(hssfRow.getCell(0)))) {
                dataMap = new LinkedHashMap<>();
                // 循环列Col
                for (int colNum = 0; colNum < dataKeys.size(); colNum++) {
                    HSSFCell hssfCell = (HSSFCell) hssfRow.getCell(colNum);
                    if(hssfCell != null){
                        if(hssfCell.getCellType() != HSSFCell.CELL_TYPE_NUMERIC){
                            hssfCell.setCellType(Cell.CELL_TYPE_STRING);
                            dataMap.put(dataKeys.get(colNum), getValue(hssfCell));
                        }else {
                            if(HSSFDateUtil.isCellDateFormatted(hssfCell)){
                                dataMap.put(dataKeys.get(colNum), getValue(hssfCell));

                            }else {
                                hssfCell.setCellType(Cell.CELL_TYPE_STRING);
                                dataMap.put(dataKeys.get(colNum), getValue(hssfCell));
                            }
                        }
                    }

                }
                dataMapList.add(dataMap);
            }
        }
        // }

        if (dataMapList == null || dataMapList.size() == 0) return null;
        return JSON.parseArray(JSON.toJSONString(dataMapList), clazz);
    }

    private static String getValue(Cell hssfCell) {

        if (hssfCell == null) {
            return null;
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            // 返回布尔类型的值
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            // 返回数值类型的值
            if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

                return sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue()));
            }
            return StringUtil.formatNumber2(hssfCell.getNumericCellValue());
//            return null;
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_STRING) {
            // 返回字符串类型的值
            return hssfCell.getStringCellValue();
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_FORMULA) {
            return hssfCell.getCellFormula();
        } else {
            return null;
        }
    }}

